package com.yikome.monitor.service;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageImpl;
import org.springframework.data.domain.Pageable;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.stereotype.Service;

import com.yikome.monitor.request.MonitorQueryRequest;
import com.google.common.base.Preconditions;
import com.yikome.utils.NamedParameterJdbcUtils;

import cn.hutool.core.util.StrUtil;

@Service
public class MonitorService {

	@Autowired
	private NamedParameterJdbcTemplate jdbcTemplate ;

	public Page<Map<String, Object>> findProductMonitor(MonitorQueryRequest monitorQueryRequest, Pageable pageable) {
		
		Preconditions.checkNotNull(monitorQueryRequest.getQueryStartTime(), "时间参数不能为空");
		Preconditions.checkNotNull(monitorQueryRequest.getQueryEndTime(), "时间参数不能为空");
		Preconditions.checkNotNull(monitorQueryRequest.getMomStartTime(), "环比时间参数不能为空");
		Preconditions.checkNotNull(monitorQueryRequest.getMomEndTime(), "环比时间参数不能为空");
		
		String groupSQL = "GROUP BY m_monitor_product_statistics.product_id \n" ; 
		
		StringBuilder paramsSQL = new StringBuilder("	WHERE 1 = 1 \n");
		Map<String , Object> paramsMap = new HashMap<>();
		
		paramsMap.put("statistics_mom_start_date", monitorQueryRequest.getMomStartTime());
		paramsMap.put("statistics_mom_end_date", monitorQueryRequest.getMomEndTime());
		paramsMap.put("statistics_start_date", monitorQueryRequest.getQueryStartTime());
		paramsMap.put("statistics_end_date", monitorQueryRequest.getQueryEndTime());
		
		if(monitorQueryRequest.getUpEndTime() != null && monitorQueryRequest.getUpStartTime() != null) {
			paramsSQL.append("AND p_product_ext.up_at BETWEEN FROM_UNIXTIME(:begainTime) AND FROM_UNIXTIME(:endTime) \n");
    		paramsMap.put("begainTime", monitorQueryRequest.getUpStartTime());
    		paramsMap.put("endTime", monitorQueryRequest.getUpEndTime());
		}
		
        if(StrUtil.isNotBlank(monitorQueryRequest.getProductId())) {
            List<String> productIds = StrUtil.split(monitorQueryRequest.getProductId().replaceAll("\\s", ","), ',');
            if(productIds.size() == 1) {
                paramsMap.put("productId", monitorQueryRequest.getProductId());
                paramsSQL.append("    AND p_product.id = :productId \n");
            }else {
                paramsMap.put("productIds", productIds);
                paramsSQL.append("    AND p_product.id IN (:productIds) \n");
            }
        }
		if(monitorQueryRequest.getProductStatus() != null) {
			paramsMap.put("productStatus", monitorQueryRequest.getProductStatus());
			paramsSQL.append("	AND p_product.`status` = :productStatus \n");
		}
		if(StrUtil.isNotBlank(monitorQueryRequest.getProductSource())) {
			paramsMap.put("productSource", monitorQueryRequest.getProductSource());
			paramsSQL.append("	AND p_product_ext.source_type = :productSource \n");
		}
		if(StrUtil.isNotBlank(monitorQueryRequest.getHostName())) {
			paramsMap.put("hostName", monitorQueryRequest.getHostName());
			paramsSQL.append("	AND m_monitor_product_statistics.host_name = :hostName \n");
		}
		if(StrUtil.isNotBlank(monitorQueryRequest.getCategoryIds())) {
			paramsMap.put("categoryIds", StrUtil.split(monitorQueryRequest.getCategoryIds(), ','));
			paramsSQL.append("	AND p_product_ext.category_id IN (:categoryIds) \n");
		}
		if(monitorQueryRequest.getMinPrice() != null) {
			paramsMap.put("minPrice", monitorQueryRequest.getMinPrice());
			paramsSQL.append("	AND p_product.price >= :minPrice \n");
		}
		if(monitorQueryRequest.getMaxPrice() != null) {
			paramsMap.put("maxPrice", monitorQueryRequest.getMaxPrice());
			paramsSQL.append("	AND p_product.price <= :maxPrice \n");
		}
		paramsSQL.append("	AND m_monitor_product_statistics.statistics_date >= :statistics_start_date AND m_monitor_product_statistics.statistics_date <= :statistics_end_date \n");
		
		String contentSQL = "SELECT \n" + 
							"  vo.product_id, vo.title, vo.image, vo.price, vo.source_type, vo.up_at, \n" + 
							"  vo.CART_DELETE, vo.`VIEW`, vo.BUY_NOW, vo.ADD_TO_CART, vo.PURCHASE, \n" + 
							"  ROUND((VO.BUY_NOW + VO.ADD_TO_CART)/VO.`VIEW` , 4) 'CLICK_RATE', ROUND(VO.PURCHASE/(VO.BUY_NOW + VO.ADD_TO_CART) , 4) 'PURCHASE_RATE', ROUND(VO.PURCHASE/VO.`VIEW` , 4) 'VIEW_PURCHASE_RATE', \n" + 
							"  VO.`CART_DELETE_MOM`, VO.VIEW_MOM, VO.BUY_NOW_MOM, VO.ADD_TO_CART_MOM, VO.PURCHASE_MOM,  \n" + 
							"  ROUND((VO.BUY_NOW_MOM + VO.ADD_TO_CART_MOM)/VO.`VIEW_MOM` , 4) 'CLICK_RATE_MOM', ROUND(VO.PURCHASE_MOM/(VO.BUY_NOW_MOM + VO.ADD_TO_CART_MOM) , 4) 'PURCHASE_RATE_MOM' , ROUND(VO.PURCHASE_MOM/VO.`VIEW_MOM` , 4) 'VIEW_PURCHASE_RATE_MOM'  \n" + 
							"FROM \n" + 
							"( \n" + 
							"	SELECT    \n" + 
							"		p_product.id product_id, p_product.title, p_product.image, p_product.price, p_product_ext.source_type,p_product_ext.up_at, \n" + 
							"		SUM(IF(m_monitor_product_statistics.event_name='CART_DELETE' , m_monitor_product_statistics.count , 0)) AS 'CART_DELETE',    \n" + 
							"		SUM(IF(m_monitor_product_statistics.event_name='VIEW' , m_monitor_product_statistics.count , 0)) AS 'VIEW',    \n" + 
							"		SUM(IF(m_monitor_product_statistics.event_name='BUY_NOW' , m_monitor_product_statistics.count , 0)) AS 'BUY_NOW',   \n" + 
							"		SUM(IF(m_monitor_product_statistics.event_name='ADD_TO_CART' , m_monitor_product_statistics.count , 0)) AS 'ADD_TO_CART',  \n" + 
							"		SUM(IF(m_monitor_product_statistics.event_name='PURCHASE' , m_monitor_product_statistics.count , 0)) AS 'PURCHASE' , \n" + 
							"		(SELECT IFNULL(SUM(count),0) FROM m_monitor_product_statistics m WHERE m.product_id = m_monitor_product_statistics.product_id AND m.event_name = 'CART_DELETE' AND m.statistics_date >= :statistics_mom_start_date AND m.statistics_date <= :statistics_mom_end_date) AS 'CART_DELETE_MOM',  \n" + 
							"		(SELECT IFNULL(SUM(count),0) FROM m_monitor_product_statistics m WHERE m.product_id = m_monitor_product_statistics.product_id AND m.event_name = 'VIEW' AND m.statistics_date >= :statistics_mom_start_date AND m.statistics_date <= :statistics_mom_end_date) AS 'VIEW_MOM',  \n" + 
							"		(SELECT IFNULL(SUM(count),0) FROM m_monitor_product_statistics m WHERE m.product_id = m_monitor_product_statistics.product_id AND m.event_name = 'BUY_NOW' AND m.statistics_date >= :statistics_mom_start_date AND m.statistics_date <= :statistics_mom_end_date) AS 'BUY_NOW_MOM',  \n" + 
							"		(SELECT IFNULL(SUM(count),0) FROM m_monitor_product_statistics m WHERE m.product_id = m_monitor_product_statistics.product_id AND m.event_name = 'ADD_TO_CART' AND m.statistics_date >= :statistics_mom_start_date AND m.statistics_date <= :statistics_mom_end_date) AS 'ADD_TO_CART_MOM',  \n" + 
							"		(SELECT IFNULL(SUM(count),0) FROM m_monitor_product_statistics m WHERE m.product_id = m_monitor_product_statistics.product_id AND m.event_name = 'PURCHASE' AND m.statistics_date >= :statistics_mom_start_date AND m.statistics_date <= :statistics_mom_end_date) AS 'PURCHASE_MOM'  \n" + 
							"	FROM m_monitor_product_statistics   \n" + 
							"		LEFT JOIN p_product ON (m_monitor_product_statistics.product_id = p_product.id) \n" + 
							"		LEFT JOIN p_product_ext ON (p_product.id = p_product_ext.product_id) \n" + 
							paramsSQL + groupSQL +
							") vo \n" ; 
		
		String orderSQL = NamedParameterJdbcUtils.findOrderSQL(pageable);
    	String pageSQL = NamedParameterJdbcUtils.findPageSQL(pageable);
		
		List<Map<String, Object>> content = jdbcTemplate.queryForList(contentSQL + orderSQL +  pageSQL , paramsMap);
		
		String countSQL = "SELECT \n" +
		                  "  p_product.id product_id, p_product.title, p_product.image, p_product.price, p_product_ext.source_type \n" +
		                  "FROM m_monitor_product_statistics \n" + 
						  "  LEFT JOIN p_product ON (m_monitor_product_statistics.product_id = p_product.id) \n" +
						  "  LEFT JOIN p_product_ext ON (p_product.id = p_product_ext.product_id) \n";
		
		Long total = jdbcTemplate.queryForObject("SELECT COUNT(1) FROM (" + countSQL + paramsSQL + groupSQL + ") T", paramsMap, Long.class);
		
		return new PageImpl<>(content, pageable, total);
	}
	
	public Map<String, Object> findProductMonitorSummary(MonitorQueryRequest monitorQueryRequest) {
		
		Preconditions.checkNotNull(monitorQueryRequest.getQueryStartTime(), "时间参数不能为空");
		Preconditions.checkNotNull(monitorQueryRequest.getQueryEndTime(), "时间参数不能为空");
		Preconditions.checkNotNull(monitorQueryRequest.getMomStartTime(), "环比时间参数不能为空");
		Preconditions.checkNotNull(monitorQueryRequest.getMomEndTime(), "环比时间参数不能为空");
		
		String groupSQL = "GROUP BY m_monitor_product_statistics.product_id \n" ; 
		
		StringBuilder paramsSQL = new StringBuilder("	WHERE 1 = 1 \n");
		Map<String , Object> paramsMap = new HashMap<>();
		
		paramsMap.put("statistics_mom_start_date", monitorQueryRequest.getMomStartTime());
		paramsMap.put("statistics_mom_end_date", monitorQueryRequest.getMomEndTime());
		paramsMap.put("statistics_start_date", monitorQueryRequest.getQueryStartTime());
		paramsMap.put("statistics_end_date", monitorQueryRequest.getQueryEndTime());
		
		if(StrUtil.isNotBlank(monitorQueryRequest.getProductId())) {
			paramsMap.put("productId", monitorQueryRequest.getProductId());
			paramsSQL.append("	AND p_product.id = :productId \n");
		}
		if(monitorQueryRequest.getProductStatus() != null) {
			paramsMap.put("productStatus", monitorQueryRequest.getProductStatus());
			paramsSQL.append("	AND p_product.`status` = :productStatus \n");
		}
		if(StrUtil.isNotBlank(monitorQueryRequest.getProductSource())) {
			paramsMap.put("productSource", monitorQueryRequest.getProductSource());
			paramsSQL.append("	AND p_product_ext.source_type = :productSource \n");
		}
		if(StrUtil.isNotBlank(monitorQueryRequest.getHostName())) {
			paramsMap.put("hostName", monitorQueryRequest.getHostName());
			paramsSQL.append("	AND m_monitor_product_statistics.host_name = :hostName \n");
		}
		if(StrUtil.isNotBlank(monitorQueryRequest.getCategoryIds())) {
			paramsMap.put("categoryIds", StrUtil.split(monitorQueryRequest.getCategoryIds(), ','));
			paramsSQL.append("	AND p_product_ext.category_id IN (:categoryIds) \n");
		}
		if(monitorQueryRequest.getMinPrice() != null) {
			paramsMap.put("minPrice", monitorQueryRequest.getMinPrice());
			paramsSQL.append("	AND p_product.price >= :minPrice \n");
		}
		if(monitorQueryRequest.getMaxPrice() != null) {
			paramsMap.put("maxPrice", monitorQueryRequest.getMaxPrice());
			paramsSQL.append("	AND p_product.price <= :maxPrice \n");
		}
		paramsSQL.append("	AND m_monitor_product_statistics.statistics_date >= :statistics_start_date AND m_monitor_product_statistics.statistics_date <= :statistics_end_date \n");
		
		//20200429 增加汇总数据
		String summaryContentSQL =  "SELECT \n" + 
									"  SUM(vo.CART_DELETE)CART_DELETE, SUM(vo.`VIEW`)VIEW, SUM(vo.BUY_NOW)BUY_NOW, SUM(vo.ADD_TO_CART)ADD_TO_CART, SUM(vo.PURCHASE)PURCHASE,  \n" + 
									"  ROUND(SUM(VO.BUY_NOW + VO.ADD_TO_CART)/SUM(VO.`VIEW`) , 4) 'CLICK_RATE', ROUND(SUM(VO.PURCHASE)/SUM(VO.BUY_NOW + VO.ADD_TO_CART) , 4) 'PURCHASE_RATE', ROUND(SUM(VO.PURCHASE)/SUM(VO.`VIEW`) , 4) 'VIEW_PURCHASE_RATE',  \n" + 
									"  SUM(VO.`CART_DELETE_MOM`)CART_DELETE_MOM, SUM(VO.VIEW_MOM)VIEW_MOM, SUM(VO.BUY_NOW_MOM)BUY_NOW_MOM, SUM(VO.ADD_TO_CART_MOM)ADD_TO_CART_MOM, SUM(VO.PURCHASE_MOM)PURCHASE_MOM, \n" +
									"  ROUND(SUM(VO.BUY_NOW_MOM + VO.ADD_TO_CART_MOM)/SUM(VO.`VIEW_MOM`) , 4) 'CLICK_RATE_MOM', ROUND(SUM(VO.PURCHASE_MOM)/SUM(VO.BUY_NOW_MOM + VO.ADD_TO_CART_MOM) , 4) 'PURCHASE_RATE_MOM' , ROUND(SUM(VO.PURCHASE_MOM)/SUM(VO.`VIEW_MOM`) , 4) 'VIEW_PURCHASE_RATE_MOM'  " + 
									"FROM \n" + 
									"( \n" + 
									"	SELECT    \n" + 
									"		p_product.id product_id, p_product.title, p_product.image, p_product.price, p_product_ext.source_type, \n" + 
									"		SUM(IF(m_monitor_product_statistics.event_name='CART_DELETE' , m_monitor_product_statistics.count , 0)) AS 'CART_DELETE',    \n" + 
									"		SUM(IF(m_monitor_product_statistics.event_name='VIEW' , m_monitor_product_statistics.count , 0)) AS 'VIEW',    \n" + 
									"		SUM(IF(m_monitor_product_statistics.event_name='BUY_NOW' , m_monitor_product_statistics.count , 0)) AS 'BUY_NOW',   \n" + 
									"		SUM(IF(m_monitor_product_statistics.event_name='ADD_TO_CART' , m_monitor_product_statistics.count , 0)) AS 'ADD_TO_CART',  \n" + 
									"		SUM(IF(m_monitor_product_statistics.event_name='PURCHASE' , m_monitor_product_statistics.count , 0)) AS 'PURCHASE' , \n" + 
									"		(SELECT IFNULL(SUM(count),0) FROM m_monitor_product_statistics m WHERE m.product_id = m_monitor_product_statistics.product_id AND m.event_name = 'CART_DELETE' AND m.statistics_date >= :statistics_mom_start_date AND m.statistics_date <= :statistics_mom_end_date) AS 'CART_DELETE_MOM',  \n" + 
									"		(SELECT IFNULL(SUM(count),0) FROM m_monitor_product_statistics m WHERE m.product_id = m_monitor_product_statistics.product_id AND m.event_name = 'VIEW' AND m.statistics_date >= :statistics_mom_start_date AND m.statistics_date <= :statistics_mom_end_date) AS 'VIEW_MOM',  \n" + 
									"		(SELECT IFNULL(SUM(count),0) FROM m_monitor_product_statistics m WHERE m.product_id = m_monitor_product_statistics.product_id AND m.event_name = 'BUY_NOW' AND m.statistics_date >= :statistics_mom_start_date AND m.statistics_date <= :statistics_mom_end_date) AS 'BUY_NOW_MOM',  \n" + 
									"		(SELECT IFNULL(SUM(count),0) FROM m_monitor_product_statistics m WHERE m.product_id = m_monitor_product_statistics.product_id AND m.event_name = 'ADD_TO_CART' AND m.statistics_date >= :statistics_mom_start_date AND m.statistics_date <= :statistics_mom_end_date) AS 'ADD_TO_CART_MOM',  \n" + 
									"		(SELECT IFNULL(SUM(count),0) FROM m_monitor_product_statistics m WHERE m.product_id = m_monitor_product_statistics.product_id AND m.event_name = 'PURCHASE' AND m.statistics_date >= :statistics_mom_start_date AND m.statistics_date <= :statistics_mom_end_date) AS 'PURCHASE_MOM'  \n" + 
									"	FROM m_monitor_product_statistics   \n" + 
									"		LEFT JOIN p_product ON (m_monitor_product_statistics.product_id = p_product.id) \n" + 
									"		LEFT JOIN p_product_ext ON (p_product.id = p_product_ext.product_id) \n" + 
									paramsSQL + groupSQL +
									") vo \n" ; 

		return jdbcTemplate.queryForMap(summaryContentSQL, paramsMap);
	}

	public Page<Map<String, Object>> findPageMonitor(MonitorQueryRequest monitorQueryRequest, Pageable pageable) {
		String contentSQL = "SELECT \n" +
							"	m_monitor_page_statistics.event_aims , \n" + 
							"   SUM(m_monitor_page_statistics.count) show_count \n" + "FROM m_monitor_page_statistics \n";

		Map<String, Object> paramsMap = new HashMap<>();
		StringBuilder paramsSQL = new StringBuilder("WHERE 1 = 1 \n");
		if (StrUtil.isNotBlank(monitorQueryRequest.getQueryStartTime()) && StrUtil.isNotBlank(monitorQueryRequest.getQueryEndTime())) {
			paramsSQL.append("AND m_monitor_page_statistics.statistics_date >= :statistics_start_date AND m_monitor_page_statistics.statistics_date <= :statistics_end_date \n");
			paramsMap.put("statistics_start_date", monitorQueryRequest.getQueryStartTime());
			paramsMap.put("statistics_end_date", monitorQueryRequest.getQueryEndTime());
		}
		
		String groupSQL = "GROUP BY m_monitor_page_statistics.event_aims \n";
		
		String orderSQL = NamedParameterJdbcUtils.findOrderSQL(pageable);
    	String pageSQL = NamedParameterJdbcUtils.findPageSQL(pageable);

		List<Map<String, Object>> content = jdbcTemplate.queryForList(contentSQL + paramsSQL + groupSQL + orderSQL + pageSQL, paramsMap);

		Long total = jdbcTemplate.queryForObject("SELECT COUNT(1) FROM (" + contentSQL + paramsSQL + groupSQL + ") T", paramsMap, Long.class);

		return new PageImpl<>(content, pageable, total);
	}

	public Page<Map<String, Object>> findEventMonitor(MonitorQueryRequest monitorQueryRequest, Pageable pageable) {
		String contentSQL = "SELECT \n" + "	m_monitor_event_statistics.event_aims , \n" + 
							"   SUM(m_monitor_event_statistics.count) click_count \n" + "FROM m_monitor_event_statistics \n";

		Map<String, Object> paramsMap = new HashMap<>();
		StringBuilder paramsSQL = new StringBuilder("WHERE 1 = 1 \n");
		if (StrUtil.isNotBlank(monitorQueryRequest.getQueryStartTime()) && StrUtil.isNotBlank(monitorQueryRequest.getQueryEndTime())) {
			paramsSQL.append(
					"AND m_monitor_event_statistics.statistics_date >= :statistics_start_date AND m_monitor_event_statistics.statistics_date <= :statistics_end_date \n");
			paramsMap.put("statistics_start_date", monitorQueryRequest.getQueryStartTime());
			paramsMap.put("statistics_end_date", monitorQueryRequest.getQueryEndTime());
		}

		String groupSQL = "GROUP BY m_monitor_event_statistics.event_aims \n";
		
		String orderSQL = NamedParameterJdbcUtils.findOrderSQL(pageable);
    	String pageSQL = NamedParameterJdbcUtils.findPageSQL(pageable);

		List<Map<String, Object>> content = jdbcTemplate.queryForList(contentSQL + paramsSQL + groupSQL + orderSQL + pageSQL, paramsMap);

		Long total = jdbcTemplate.queryForObject("SELECT COUNT(1) FROM (" + contentSQL + paramsSQL + groupSQL + ") T", paramsMap, Long.class);

		return new PageImpl<>(content, pageable, total);
	}
	
}